import sqlite3

import tushare as ts

from comm.conn import Conn
from comm.datefunc import Date
from conf.settings import confs
import comm.loggers
import logging
import pandas as pd

logger = logging.getLogger(__name__)


class Tsapi(object):
    def __init__(self):
        ts.set_token(confs['api_token'])
        self.ts = ts
        self.apis = ts.pro_api()
        self.conn = Conn()

    def get_stock_list(self, tb_nm='stocks'):
        if self.conn.is_exists("stocks"):
            last_dt = f"select max(crt_dt) max_dt from {tb_nm}"
            max_dt = self.conn.query_one(last_dt)
            diff_days = Date().compare(max_dt, 'day')
            if diff_days <= 2:
                logger.info("从数据库中获取股票列表")
                return pd.read_sql_table(tb_nm, self.conn.engine)
        stocks = self.apis.stock_basic(exchange='', list_status='L')
        stocks.rename(columns={'list_date': 'beg_dt', 'symbol': 'stock_cd', 'name': 'stock_nm'}, inplace=True)
        stocks['crt_dt'] = Date().batch_dt()
        stocks.to_sql('stocks', self.conn.get_conn_refresh(), if_exists='replace', index=False)
        logger.info('从网络获取最新股票列表，并更新股票列表')
        return stocks
    
    def get_trade_dtl(self,start_dt=None,end_dt=None):
        if start_dt is not None and end_dt is not None:
            if end_dt<start_dt:
                return None
            sql=f"select * from stock_trade where trade_date between {start_dt} and {end_dt}"
        else:
            start_dt=Date().add_mons(-2,return_last_day=True).replace("-","")
            sql=f"select * from stock_trade where trade_date>={start_dt}"
        df=self.conn.select(sql)
        return df
            


if __name__ == '__main__':
    ts = Tsapi()

    ts.get_trade_dtl()
